<?php
JLoader::register("QueueHelper", JPATH_SITE.'/components/com_jobmail/helpers/queue.php');
class PResumesHelper
{
   public static function showBreadcrumb() {
      $breadcrumb = array();
      $jobid = JRequest::getInt('jobid', 0);
      
      if($jobid <= 0)
         return $breadcrumb;

      $user = PFactory::getMasterUser();
      $db = PFactory::getDbo();

      $companyid = $user->id; 

      $query = "SELECT j.job_title " .
               " FROM #__jobs_job AS j " .
               " WHERE j.id = " . intval($jobid) . 
               " AND j.employer_id = " . intval($companyid); 

      $db->setQuery($query);
      $job_title = $db->loadResult();
      if(!$job_title)
         return $breadcrumb;

      $breadcrumb[] = array('position'=> $jobid, 'name' => $job_title);

      return $breadcrumb; 

   }

   public static function getQuota() {
      $user = PFactory::getMasterUser(); 

      if(!PFactory::isAdminUser())
         return 0; 

      $app = PFactory::getApplication(); 
      if($app->getUserState("quota", null) != null)
         return $app->getUserState("quota"); 

      // get from db 
      $resumeQuota=OC_Preferences::getValue($user->id, 'resumes', 'quota', 'default');
      if($resumeQuota=='default') {
         $resumeQuota=OC_AppConfig::getValue('resumes', 'default_quota', 'none');
      }
      if($resumeQuota=='none') {
         $resumeQuota = -1;
      }

      $app->getUserState("quota", $resumeQuota);
      return $resumeQuota;
   }

   /**
    * get the free space in the company's resumes cloud - based on how manu resumes can be stored 
    * @param path
    * @return int
    */
   public static function getFreeSpace() {
      $quota = self::getQuota(); 

      if($quota <= 0)
         return $quota; 

      /* get total number of resumes saved */
      $user = PFactory::getUser();
      $db = PFactory::getDbo();

      $query = "SELECT COUNT(*) as num FROM *PREFIX*hr_jobseeker WHERE is_saved=1 AND company_id=" . $user->id; 
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $num_saved = $db->loadResult(); 

      return $quota  - $num_saved;
   }

   /**
    * Calculate the resume space
    */
   public static function getStorageInfo() {
      $quota = self::getQuota();
      $free = self::getFreeSpace();
      $total = $quota; 
      if ($quota <= 0) {
         $total = 1;  // prevent division by zero
         $used = 0; 
      }
      else {
         $used = $quota - $free; 
      }

      $percent = round(($used / $total)) * 100;

      return array('free' => $free, 'used' => $used, 'total' => $quota, 'percent' => $percent);
   }

   /* 
    * get order by mapping 
    */
   public static function getOrderMapping(&$orderby, &$orderdir) {
      switch($orderby) {
         case 'workexp':
            $orderby = 's.workexp_months';
            break;
         case 'degree':
            $orderby = 'degree_table.id';
            break;
         case 'onlinetest':
            $orderby = 's.online_interview_completion';
            break;
         case 'matchresult':
            $orderby = 's.match_degree';
         break;
         case 'added':
         default:
            $orderby = 's.date_added';
         break;
      }

      switch($orderdir) {
         case 'ASC':
            $orderdir = 'ASC';
         break;
         default:
            $orderdir = 'DESC';
         break;
      }

   }

   /*
    * get order by and order direction 
    */
   public static function getListOrder() {
      $app = PFactory::getApplication();
      $resumedir = $app->getUserState('resumedir', null);

      $defaultorders = array(
                        "s.match_degree" => "DESC",
                        "s.online_interview_completion"=>"DESC", 
                        "s.workexp_months"=>"DESC", 
                        "degree_table.id"=>"DESC",
                        "s.date_added" => "DESC"
                        ); 

      $orderby = JArrayHelper::getValue($resumedir, 'orderby', 'added');
      $orderdir = strtoupper(JArrayHelper::getValue($resumedir, 'orderdir', 'desc'));
      self::getOrderMapping($orderby, $orderdir);

      // handle priority order/dir too 
      $porderby =  JArrayHelper::getValue($resumedir, 'porderby', null);
      $porderdir =  strtoupper(JArrayHelper::getValue($resumedir, 'porderdir', null));
      if(!empty($porderby) && !empty($porderdir)) {
         self::getOrderMapping($porderby, $porderdir);
      }

      $listorders = array();
      foreach($defaultorders as $k=>$v) {
         if($porderby && $porderby == $k) 
            continue; 
         else if(!$porderby && $orderby == $k)
            continue; 

         $listorders[] = "$k $v";
      }
   

      // delaysort is for short list sorting, mainly used for top 10 searching (which match_degree order by must be performed first) 
      $delaysort = null; 
      if(!empty($porderby) && !empty($porderdir)) {
         array_unshift($listorders, $porderby." ".$porderdir);
         $delaysort = array('orderby'=>str_replace("s.", '', $orderby), 'orderdir'=>$orderdir);
      }
      else {
         array_unshift($listorders, $orderby." ".$orderdir);
      }

      return array($listorders, $delaysort);
   }

   public static function sortList(&$array, $delaysort=array()) {
      if(empty($array) || empty($delaysort))
         return; 

      usort($array, function ($a, $b) use ($delaysort) {
         if($delaysort['orderdir'] == 'ASC') {
            return is_string($a->$delaysort['orderby']) ?
                     strcmp($a->$delaysort['orderby'], $b->$delaysort['orderby']) :
                     ($a->$delaysort['orderby'] - $b->$delaysort['orderby']);
         }
         else {
            return is_string($a->$delaysort['orderby']) ?
                     strcmp($b->$delaysort['orderby'], $a->$delaysort['orderby']) :
                     ($b->$delaysort['orderby'] - $a->$delaysort['orderby']);
         }
      });

   }

   public static function getTimeRange($time='today')
   {
      $endtime = PFactory::getDate()->toUnix();

      $config = PFactory::getConfig();
      $today = PFactory::getDate('today', $config->get('offset'))->toUnix(); 
 
      //just today
      if($time == "today") {
         $starttime = $today;
      }
      else if($time == "1d"){
         // start from beginning of yesterday
         $starttime = $today - 24 * 60 * 60; 
      }
      else if($time == "2d") {
         $starttime = $today - 2 * 24 * 60 * 60; 
      }
      else if($time == "3d") {
         $starttime = $today - 3 * 24 * 60 * 60; 
      }
      else if($time == "1w") {
         $starttime = $today - 7 * 24 * 60 * 60; 
      }
      else if($time == "2w") {
         $starttime = $today - 2* 7 * 24 * 60 * 60; 
      }
      else if($time == "1m" || $time == "4w") {
         $starttime = PFactory::getDate('-1 month')->toUnix();
      }
      else if($time == "2m" || $time == "8w") {
         $starttime = PFactory::getDate('-2 month')->toUnix();
      }
      else if($time == "3m") {
         $starttime = PFactory::getDate('-3 month')->toUnix();
      }
      else if($time == "6m" || $time == "24w") {
         $starttime = PFactory::getDate('-6 month')->toUnix();
      }
      else if($time == "all") {
         $starttime = PFactory::getDate('-12 month')->toUnix();
      }
      else {
         // default 
         $starttime = $today;
      }

      // just today
      return array($starttime, $endtime);
   }
   
   public static function getDegreeLevelRange($degreeLevel)
   {
      $degreeLevelRange = array();
      switch ($degreeLevel) {
         default:
         case 'senior_middle':
            // min: degree included, max: degree excluded, use maxi if degree is included 
            $degreeLevelRange = array('min'=>'高中', 'max'=>'其他');
            break;
         case 'college':
            $degreeLevelRange = array('min'=>'大专', 'max'=>'其他');
            break;
         case 'bachelor':
            $degreeLevelRange = array('min'=>'本科', 'max'=>'其他');
            break;
         case 'master':
            $degreeLevelRange = array('min'=>'硕士', 'max'=>'其他');
            break;
         case '1':
            $degreeLevelRange = array('min'=>'初中', 'max'=>'其他');
            break;
         case '2':
            $degreeLevelRange = array('min'=>'高中', 'max'=>'其他');
            break;
         case '3':
            $degreeLevelRange = array('min'=>'中技', 'max'=>'其他');
            break;
         case '4':
            $degreeLevelRange = array('min'=>'中专', 'max'=>'其他');
            break;
         case '5':
            $degreeLevelRange = array('min'=>'大专', 'max'=>'其他');
            break;
         case '6':
            $degreeLevelRange = array('min'=>'本科', 'max'=>'其他');
            break;
         case '7':
            $degreeLevelRange = array('min'=>'硕士', 'max'=>'其他');
            break;
         case '8':
            $degreeLevelRange = array('min'=>'MBA', 'max'=>'其他');
            break;
         case '9':
            $degreeLevelRange = array('min'=>'博士', 'max'=>'其他');
            break;
      }
      return $degreeLevelRange;
   }
   
   public static function getResumeStatuses() {
      $db = PFactory::getDbo();
      $query = "SELECT GROUP_CONCAT(id ORDER BY id ASC) AS status_ids, GROUP_CONCAT(title ORDER BY id ASC) AS status_titles FROM *PREFIX*hr_jobseeker_status GROUP BY groupname ORDER BY id ASC";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObjectList();
   }
   
   public static function showResumeStatuses($resumeStatuses, $ajaxMode = 0, $offsite = 0) {
      if (empty($resumeStatuses)) {
         return ;
      }
      
      $curStatus = JRequest::getInt('status_id', 0);
      
      if (!$ajaxMode) {
         $searchUrl = PResumesHelper::getSearchUrl($offsite);
      }

      echo '<div class="resumestatus_option popup">';
      
      for ($i=0; $i<count($resumeStatuses); $i++) {
         if ($i>0) {
            echo '<b></b>';
         }
         
         $status_ids = explode(',', $resumeStatuses[$i]->status_ids);
         $status_titles = explode(',', $resumeStatuses[$i]->status_titles);

         for ($j=0; $j<count($status_ids); $j++) {
            if ($ajaxMode) {
               echo '<a class="resume_status_option_child" data-status-id="'.$status_ids[$j].'" href="javascript:;">'.$status_titles[$j].'</a>';
            } else {
               if ($curStatus && $curStatus == $status_ids[$j]) {
                  list($href, $selected) = PResumesHelper::getSearchItemHref($searchUrl, 'status_id', 'all');
                  $selected = 1;
               } else {
                  list($href, $selected) = PResumesHelper::getSearchItemHref($searchUrl, 'status_id', $status_ids[$j]);
                  $selected = 0;
               }
               
               echo '<a href="'.$href.'" '.($selected ? 'class="selected"' : '').'>'.$status_titles[$j].'</a>';
            }
         }
      }
      
      echo '</div>';
   }
   
   public static function getJob($jobid = 0) {
      if(empty($jobid))
         return null;
       
      $user = PFactory::getMasterUser();
      $db = PFactory::getDbo();
      $query = 'SELECT job_title, id_city, left(ikey, 1) AS ikey FROM #__jobs_job WHERE id=' . intval($jobid) . ' AND employer_id=' . intval($user->id);
      $db->setQuery($query);
      return $db->loadObject();
   }
    
   public static function getTag($tagname = null) {
      if(empty($tagname))
         return null;
       
      $user = PFactory::getMasterUser();
      $db = PFactory::getDbo();
      $query = 'SELECT name, left(ikey, 1) AS ikey FROM *PREFIX*hr_jobseeker_tag_term WHERE verified=1 AND (owner=0 OR owner=' . intval($user->id) . ') AND name=' .$db->quote($tagname);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObject();
   }
   
   public static function getJobList($companyid, $joblimit = 10, $keyword = '', $jobcity = '', $jobikey = '',$source = null, $showall = false,$nojob=true) {
      $db = PFactory::getDbo();
      $dir = JRequest::getString('dir', 'all');
      
      $leftSql = '';
      $orderBy ='';
      $whereArr = array();
      
      array_push($whereArr, 'jj.is_active="y"');
      array_push($whereArr, 'jj.employer_id=' . intval($companyid));
      
      if (!$showall) {
         if ($dir == 'all' || $dir == 'trashed') {
            if(PCompanyHelper::isJobManager() && !PFactory::isMasterUser()) {
               $subuser = PCompanyHelper::getSubUser();
               if(!empty($subuser) && !empty($subuser->assignedjobs)) {
                  array_push($whereArr, 'jj.id IN (' . implode(',', $subuser->assignedjobs) . ')');
               } else {
                  array_push($whereArr, 'jj.id = -1');
               }
            }
         } elseif ($dir == 'saved') {
            $leftSql = " LEFT JOIN *PREFIX*hr_resume_folder AS rf ON (rf.folder_name='我的收藏' AND rf.owner=" . $db->quote(OC_User::getUser()) . " AND rf.system=1) 
                         LEFT JOIN *PREFIX*hr_resume_folder_files AS rff ON rff.folder_id=rf.folder_id 
                         LEFT JOIN *PREFIX*hr_jobseeker AS hj ON hj.job_id=jj.id AND hj.id=rff.jobseeker_id";
            array_push($whereArr, 'hj.id IS NOT NULL');
         } elseif ($dir == 'shared') {
            $leftSql = " LEFT JOIN *PREFIX*share AS sae ON sae.share_with=" . $db->quote(OC_User::getUser()) .
                       " LEFT JOIN *PREFIX*hr_jobseeker AS hj ON (hj.id=sae.item_source AND hj.job_id=jj.id)";
            array_push($whereArr, 'hj.id IS NOT NULL');
         } elseif (is_numeric($dir) && $dir > 0) {
            $leftSql .= " LEFT JOIN *PREFIX*hr_resume_folder AS rfd ON (rfd.folder_id=" . intval($dir) . " AND rfd.owner=" . $db->quote(OC_User::getUser()) . " )
                          LEFT JOIN *PREFIX*hr_resume_folder_files AS rffd ON rffd.folder_id=rfd.folder_id
                          LEFT JOIN *PREFIX*hr_jobseeker AS hj ON (hj.job_id=jj.id AND hj.id=rffd.jobseeker_id)";
            array_push($whereArr, 'hj.id IS NOT NULL');
         }
      }
      
      if (!empty($keyword)) {
         array_push($whereArr, 'job_title LIKE ' . $db->quote('%' . $keyword . '%'));
      }
      
      if (!empty($jobcity)) {
         $muniProvinceId = ProvinceHelper::getMuniProvinceId($jobcity);
         if ($muniProvinceId) {
            array_push($whereArr, '(id_city=' . intval($jobcity) . ' OR id_state=' . intval($muniProvinceId) . ')');
         } else {
            array_push($whereArr, 'id_city=' . intval($jobcity));
         }
      }
      
      if (!empty($jobikey)) {
         if ($jobikey == 'other') {
            array_push($whereArr, 'ikey=""');
         } else {
            array_push($whereArr, 'ikey LIKE ' . $db->quote($jobikey . '%'));
         }
      }
      //if $nojob is true,we get joblist without system equal 1,
      //else order by system desc in order to search in resumelist
      if($nojob){
         array_push($whereArr, 'jj.system=0');        
      }else{
         $orderBy= 'jj.system DESC,';
      }
      
      array_push($whereArr, 'jj.is_trashed=0');

      $sourcesql = '';
      if(!empty($source)){
         $sourcesql = ' AND LOCATE('.$db->quote($source).', jj.source) > 0 ';
      }
      
      $query = 'SELECT DISTINCT jj.id, jj.job_title FROM #__jobs_job AS jj ' . $leftSql . ' WHERE ' . implode(" AND " , $whereArr) . $sourcesql.
               ' GROUP BY jj.id ORDER BY '.$orderBy.'jj.publish_date DESC, jj.id DESC';
      
      if ($joblimit) {
         $query .= ' LIMIT ' . $joblimit;
      }
      
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList();
   }
   
   public static function getTagList($companyid, $taglimit = 10, $keyword = '', $tagikey = '', $showall = false) {
      $db = PFactory::getDbo();
      $dir = JRequest::getString('dir', 'all');
      
      $leftSql = '';
      $whereArr = array();
      
      array_push($whereArr, 't.verified=1');
      array_push($whereArr, 'd.owner=' . intval($companyid) );
      
      if (!$showall) {
         if ($dir == 'all' || $dir == 'trashed') {
            if(PCompanyHelper::isJobManager() && !PFactory::isMasterUser()) {
               $subuser = PCompanyHelper::getSubUser();
               $leftSql = "LEFT JOIN *PREFIX*hr_jobseeker AS hj ON hj.id=d.jobseeker_id";
               if(!empty($subuser) && !empty($subuser->assignedjobs)) {
                  array_push($whereArr, 'hj.job_id IN (' . implode(',', $subuser->assignedjobs) . ')');
               } else {
                  array_push($whereArr, 'hj.job_id = -1');
               }
            }
         } elseif ($dir == 'saved') {
            $leftSql = " LEFT JOIN *PREFIX*hr_resume_folder AS rf ON (rf.folder_name='我的收藏' AND rf.owner=" . $db->quote(OC_User::getUser()) . " AND rf.system=1)
                         LEFT JOIN *PREFIX*hr_resume_folder_files AS rff ON rff.folder_id=rf.folder_id
                         LEFT JOIN *PREFIX*hr_jobseeker AS hj ON (hj.id=d.jobseeker_id AND hj.id=rff.jobseeker_id)";
            array_push($whereArr, 'hj.id IS NOT NULL');
         } elseif ($dir == 'shared') {
            $leftSql = " LEFT JOIN *PREFIX*share AS sae ON sae.share_with=" . $db->quote(OC_User::getUser()) .
                       " LEFT JOIN *PREFIX*hr_jobseeker AS hj ON (hj.id=d.jobseeker_id AND hj.id=sae.item_source)";
            array_push($whereArr, 'hj.id IS NOT NULL');
         } elseif (is_numeric($dir) && $dir > 0) {
            $leftSql .= " LEFT JOIN *PREFIX*hr_resume_folder AS rfd ON (rfd.folder_id=" . intval($dir) . " AND rfd.owner=" . $db->quote(OC_User::getUser()) . " )
                          LEFT JOIN *PREFIX*hr_resume_folder_files AS rffd ON rffd.folder_id=rfd.folder_id
                          LEFT JOIN *PREFIX*hr_jobseeker AS hj ON (hj.id=d.jobseeker_id AND hj.id=rffd.jobseeker_id)";
            array_push($whereArr, 'hj.id IS NOT NULL');
         }
      }
      
      if (!empty($keyword)) {
         array_push($whereArr, 't.name LIKE ' . $db->quote('%' . $keyword . '%'));
      }
      
      if (!empty($tagikey)) {
         if ($tagikey == 'other') {
            array_push($whereArr, 't.ikey=""');
         } else {
            array_push($whereArr, 't.ikey LIKE ' . $db->quote($tagikey . '%'));
         }
      }
      
      $query = 'SELECT DISTINCT t.name FROM *PREFIX*hr_jobseeker_tag_term AS t' .
               ' LEFT JOIN *PREFIX*hr_jobseeker_tag_term_details AS d ON d.tagid=t.id ' . $leftSql . ' WHERE ' . implode(" AND " , $whereArr) .
               ' GROUP BY t.id ORDER BY t.owner DESC, t.hits DESC';
      
      if ($taglimit) {
         $query .= ' LIMIT ' . $taglimit;
      }
      
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList();
   }
   
   public static function getJobCityList($companyid, $jobmanager = false, $uid = '', $cityids = array())
   {
      if (!empty($cityids) && in_array('other', $cityids)) {
         JArrayHelper::toInteger($cityids);
      }
      $db = PFactory::getDbo();
      $query = 'SELECT IF(jj.id_city, c.cityid, "other") AS cityid, IF(jj.id_city, c.city, "其他") AS city, p.province FROM #__jobs_job AS jj ' .
               ' LEFT JOIN #__citylist AS c ON c.cityid=jj.id_city ' .
               ' LEFT JOIN #__provincelist AS p ON p.provinceid=c.provinceid ' .
               ($jobmanager ? ' LEFT JOIN *PREFIX*hr_jobmanager AS jm ON jm.jobid=jj.id ' : '') . 
               ' WHERE jj.is_trashed=0 AND jj.system=0 AND jj.is_active="y" AND jj.employer_id=' . intval($companyid) . (!empty($cityids) ? ' AND jj.id_city IN (' . implode(',', $cityids) . ')' : '') .
               ($jobmanager ? ' AND jm.manager=' . $db->quote($uid) : '') . ' GROUP BY jj.id_city ORDER BY cityid ASC';
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList('cityid');
   }
   
   public static function getJobIkeyList($companyid, $jobmanager = false, $uid = '')
   {
      $db = PFactory::getDbo();
      $query = 'SELECT DISTINCT IF(LENGTH(ikey)>0, left(ikey, 1), "other") AS ikey FROM #__jobs_job AS jj ' .
               ($jobmanager ? ' LEFT JOIN *PREFIX*hr_jobmanager AS jm ON jm.jobid=jj.id ' : '') .
               ' WHERE jj.is_trashed=0 AND jj.system=0 AND jj.is_active="y" AND jj.employer_id=' . intval($companyid) . 
               ($jobmanager ? ' AND jm.manager=' . $db->quote($uid)  : '') . 
               ' ORDER BY (CASE WHEN LENGTH(ikey)>0 THEN 0
                           ELSE 1
                           END), ikey ASC';
       
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList('', 'ikey');
   }
   
   public static function getTagIkeyList($companyid)
   {
      $db = PFactory::getDbo();
      $query = 'SELECT DISTINCT IF(LENGTH(ikey)>0, left(ikey, 1), "other") AS ikey FROM *PREFIX*hr_jobseeker_tag_term
                WHERE verified=1 AND (owner=0 OR owner=' . intval($companyid) . ')
                ORDER BY (CASE WHEN LENGTH(ikey)>0 THEN 0
                          ELSE 1
                          END), ikey ASC';
       
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList('', 'ikey');
   }
   
   // build search url, which carry original url params
   public static function getSearchUrl($offsite=0) {
      if ($offsite) {
         $home_url = OC_Helper::linkToRoute('resumes_offsitesearch');
      } else {
         $home_url = OC_Helper::linkToRoute('resumes_search');
      }
      $JURI = JURI::getInstance();
      $curPath  = $JURI->toString(array('path'));
      if ($curPath == $home_url) {
         $searchUrl = $JURI->toString(array('path','query'));
      } else {
         $curQuery = $JURI->toString(array('query'));
         $searchUrl = $home_url . $curQuery;
      }
      
      return $searchUrl;
   }
   
   // generate search item link href and return selected status, selected means this param is set
   public static function getSearchItemHref($searchUrl, $key, $value = 'all') {
      if (empty($searchUrl) || empty($key)) {
         return null;
      }
      
      $JURI = new JURI($searchUrl);
      $curQueryArr = $JURI->getQuery(true);
      if (array_key_exists('limitstart', $curQueryArr)) {
         unset($curQueryArr['limitstart']);
      }
      
      $selected = 0;
      $newQueryArr = $curQueryArr;
      $value = safeUrlDecode($value);
      
      if ($value === 'all') {
         if (!array_key_exists($key, $curQueryArr)) {
            $selected = 1;
         }
         unset($newQueryArr[$key]);
      } else {
         if (array_key_exists($key, $curQueryArr)) {
            if ($curQueryArr[$key] == $value) {
               $selected = 1;
            } else {
               if ($key === 'jobid') {
                  unset($newQueryArr['jobpos']);
               }
            }
         }
         $newQueryArr[$key] = $value;
      }

      // safe urlencode all query vars
      foreach ($newQueryArr as $key => $var) {
         $newQueryArr[$key] = safeUrlEncode($var);
      }
      
      $JURI->setQuery($newQueryArr);
      $href = $JURI->toString(array('path','query'));
      
      return array($href, $selected);
      
   }
   
   public static function isSearching() {
      $app = PFactory::getApplication();
      $resumedir = $app->getUserState('resumedir', null);
      $isSearching = JArrayHelper::getValue($resumedir, 'searching', 0);
      return $isSearching;
   }
   
   public static function getDirInfo($fid = 0, $uid = '') {
      if (empty($fid))
         return null;
      
      if(empty($uid)) {
         $uid = OC_User::getUser();
      }
      
      $db = PFactory::getDbo();

      // join delaytask to see if the fid is auto-created subfolder name based on task 
      $query = "SELECT f.folder_id AS id, IF(d.summary IS NOT NULL, d.summary, f.folder_name) AS foldername " . 
               " FROM *PREFIX*hr_resume_folder AS f " .
               " LEFT JOIN #__delaytasks AS d ON CONCAT('_t', d.id) = f.folder_name" . 
               " WHERE f.owner=" . $db->quote($uid) . " AND f.folder_id=" . intval($fid);

      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssoc();
   }
   
   public static function getCustomFolderList($uid = '', $limit = 0) {
      if(empty($uid)) {
         $uid = OC_User::getUser();
      }
      
      $db = PFactory::getDbo();
      $query = "SELECT folder_id, folder_name, num FROM *PREFIX*hr_resume_folder WHERE (system=0 OR folder_name='导入文件夹') AND owner=" . $db->quote($uid) . 
               "ORDER BY CASE WHEN folder_name='导入文件夹' THEN 0
                         ELSE 1
                         END"; 
      
      if ($limit) {
         $query .= ' LIMIT ' . $limit;
      }
      
      $cache = CacheHelper::getCache('app_resumes');
      $data = $cache->call(array('PResumesHelper', '_getCustomFolderList'), $query);
      CacheHelper::setTriggers($uid, $cache, array('*PREFIX*hr_resume_folder'));
      return $data;
   }
   
   public static function _getCustomFolderList($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObjectList();
   }
   
   public static function getSavedFolderID($uid = "") {
      if(empty($uid)) {
         $uid = OC_User::getUser();
      }
      
      $db = PFactory::getDbo();
      $db->stayOnMaster();
      $query = "SELECT folder_id FROM *PREFIX*hr_resume_folder WHERE folder_name='我的收藏' AND system=1 AND owner=" . $db->quote($uid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $saveFolderID = $db->loadResult();
      if (!$saveFolderID) {
         // create saved folder
         $query = "INSERT INTO *PREFIX*hr_resume_folder (folder_name, owner, system) VALUES ('我的收藏', " . $db->quote($uid) . ", 1)";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if (!$db->execute()) {
            $db->leaveOnMaster();
            return false;
         }
         $query = 'SELECT LAST_INSERT_ID()';
         $db->setQuery($query);
         $saveFolderID = $db->loadResult();
         CacheHelper::triggerRemove($uid, array('*PREFIX*hr_resume_folder'));
      }
      
      $db->leaveOnMaster();
      return $saveFolderID;
   }
   
   public static function isSharedResume($uid, $resumeid) {
      if (empty($uid))
         return false;
   
      $db = PFactory::getDbo();
      $query = "SELECT COUNT(*) FROM *PREFIX*share WHERE item_type='resumes' AND item_source=" . intval($resumeid) . " AND share_with=" . $db->quote($uid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadResult() ? true : false;
   }

   public static function checkResumeUploadPermission($jobseekerinfo, $new_jobid=0) {
      // upload/modify resume for new $jobid
      if($new_jobid && !PCompanyHelper::checkJobManagerPermissions($new_jobid))
         return false;
      // empty jobseeker, allow to upload if it's job position admin
      if(empty($jobseekerinfo))
         return PCompanyHelper::isJobAdmin();
      // job manager, can manage this jobseekerinfo 
      if(PCompanyHelper::checkJobManagerPermissions($jobseekerinfo->job_id))
         return true;
      // creator 
      if($jobseekerinfo->creator == OC_User::getUser())
         return true;
      return false;
   }

   public static function updateSearchTask($action, $term_id, $type, $delaytask_ids=array(), $termchanged=true, $delayjob=false) {
      if(!$action) 
         return false; 

      if (!empty($delaytask_ids)) {
         // Add task to beanstalkd tube
         try {
            $queue = new QueueHelper();
            $data = array();
            $data['action'] = $action;
            $data['term_id'] = intval($term_id);
            $data['delaytask_ids'] = $delaytask_ids;
            $data['source'] = $type ? $type : 'all'; 
            # if a term is not changed but we were asked to restart, we don't have to 
            $data['termchanged'] = $termchanged ? 1 : 0; 

            $tubename = 'localsearchmonitorTube'; 

            $config = PFactory::getConfig();
            // need to check if global queue enabled. If not, everything should be in localhost beanstalkd 
            if($config->get('globalqueue')) {
               $task = array();
               $task['tube'] = $tubename;
               $task['data'] = $data;
               $task['companyid'] = PFactory::getMasterUser()->id;
               $data = $task; 
               $tubename = 'globaltaskTube'; 
            }
 
            # set a delay task with 5 seconds.  If a page-reload happens, this kill job will be be canceled 
            $delayjob_time = $delayjob ? 5 : 0; 
            $queue->putTask($data, $tubename, 0, $delayjob_time);
         } catch(Exception $e) {}

         return true;
      }
   }

   public static function updateTermSyncStatus($termid, $type, $action="", $termchanged=true) {
      if (empty($termid) || empty($type)) {
         return false;
      }
      $db=PFactory::getDbo();
      $master_user = PFactory::getMasterUser()->get('id');
      $query = "SELECT last_sync_result FROM *PREFIX*hr_search_terms WHERE id = ".intval($termid)." AND userid = ".$db->quote($master_user);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $last_sync_result = $db->loadResult();

      $last_sync_result = !empty($last_sync_result) ? json_decode($last_sync_result, true) : array();

      if($action == 'remove') {
         // we need to kill this delay task first 
         $v = isset($last_sync_result[$type]) ? $last_sync_result[$type] : array(); 
         if(!empty($v['delaytaskid'])) {
           PResumesHelper::updateSearchTask('remove', $termid, $type, array($v['delaytaskid']), false, false);
         }

         unset($last_sync_result[$type]);
      }
      else {
         // we need to send restart job to restart this associated job 
         if($action == 'restart') {
            $v = isset($last_sync_result[$type]) ? $last_sync_result[$type] : array(); 
            if(!empty($v['delaytaskid'])) {
               PResumesHelper::updateSearchTask('restart', $termid, $type, array($v['delaytaskid']), $termchanged, false);
               // delete all old result when termchanged is true 
               if($termchanged) {
         
                  // we need to reset there along with search_result. The synctool will reset again to 
                  // empty out all data generated in the window of beanstalkd job delivery          
                  $last_sync_result[$type]['totalrecords'] = 0;
                  $last_sync_result[$type]['totalpages'] = 0;

                  $last_sync_result[$type]['runpage'] = 1;
                  $last_sync_result[$type]['nowpage'] = 1;
                  $last_sync_result[$type]['downnumber'] = 0;
                  $last_sync_result[$type]['result'] = 'syncing';

                  $query = "DELETE FROM *PREFIX*hr_search_result " . 
                           " WHERE user_id =" . $db->quote($master_user) . 
                           " AND source = " . $db->quote($type) . 
                           " AND term_id = " . $db->quote($termid); 

                  $db->setQuery(PUtil::replaceOCPrefix($query));
                  $db->execute();
               }
               else {
                  // set now page to be 1 for no termchange click 
                  $last_sync_result[$type]['nowpage'] = 1;
               }

               // we only reset status here. All other parameter will be handled by syncmonitor
               // set status to not 0 to avoid killed by delaytaskworker monitoring 
               $query = "UPDATE #__delaytasks SET status=1 WHERE id = " . intval($v['delaytaskid']); 
               $db->setQuery(PUtil::replaceOCPrefix($query));
               $db->execute();
            }

         }
         else {
            // when we reach here it's for new source without delaytask 
            $last_sync_result[$type]['nowpage'] = 1;
            $last_sync_result[$type]['result'] = 'syncing';
            $last_sync_result[$type]['msg'] = '';
            $last_sync_result[$type]['time'] = time();
         }
      }

      $query = "UPDATE *PREFIX*hr_search_terms SET last_sync_result = ".$db->quote(json_encode($last_sync_result))." WHERE id = ".intval($termid)." AND userid = ".$db->quote($master_user);

      if (!empty($query)) {
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if (!$db->execute()) {
            return false;
         } else {
            return true;
         }
      }

      return false;
   }

   public static function updateTermTaskStatus($task_id,$source,$actions,$param) {
      // Update oc_hr_search_terms add delaytaskid into it
      if (in_array('searchresumes', $actions)) {
         $tid = $param['tid'];

         $db=PFactory::getDbo();
         // Get old last_sync_result
         $query = "SELECT last_sync_result FROM oc_hr_search_terms WHERE id = ".intval($tid);
         $db->setQuery($query);
         $last_sync_result = $db->loadResult();
         $last_sync_result = json_decode($last_sync_result, true);
         // Insert delaytaskid into last_sync_result array
         $last_sync_result[$source]['delaytaskid'] = intval($task_id);

         $query = "UPDATE oc_hr_search_terms SET last_sync_result = ".$db->quote(json_encode($last_sync_result))." WHERE id = ".intval($tid);
         $db->setQuery($query);
         if (!$db->execute()) {
            return false;
         }
      }
   }

   public static function updateProcessed($resumeid = 0, $companyid = 0) {
      if (empty($resumeid))
         return false;
      if (empty($companyid))
         $companyid = PFactory::getMasterUser()->id;
   
      $db = PFactory::getDbo();
      $query = "UPDATE *PREFIX*hr_jobseeker set date_processed=UTC_TIMESTAMP() WHERE id=" . intval($resumeid) . " AND company_id=" .intval($companyid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
   }

   public static function setResumeStatus($resumeid=0, $status_id=1) {
      $db = PFactory::getDbo();
   
      $user = PFactory::getMasterUser();
   
      $query = "UPDATE *PREFIX*hr_jobseeker SET status_id=" . $status_id . ", date_statuschanged=UTC_TIMESTAMP() WHERE id=" . intval($resumeid) . " AND company_id=" . $user->id ." and source=@resumesource := source;";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if ($db->execute()) {
         $db->setQuery($db->useLastConnection('SELECT @resumesource'));
         $resumesource = $db->loadResult();
         // Event Driven Cache/Object Caching - clear any cache related to affected tables 
         CacheHelper::triggerRemove($user->id, array('*PREFIX*hr_jobseeker', '*PREFIX*hr_jobseeker-'.$resumeid));
         PResumesHelper::updateProcessed($resumeid);
         try {
            if(strpos($resumesource,'lagou')!== FALSE){
               $queue = new QueueHelper();
               $data = array();
               $data['resumeid'] = $resumeid;
               $data['status_id'] = $status_id;
               $tubename = 'localremoteupdateTube';
               $config = PFactory::getConfig();
               // need to check if global queue enabled. If not, everything should be in localhost beanstalkd 
               if($config->get('globalqueue')) {
                  $task = array();
                  $task['tube'] = $tubename;
                  $task['data'] = $data;
                  $task['companyid'] = PFactory::getMasterUser()->id;
                  $data = $task;
                  $tubename = 'globaltaskTube';
               }

               $queue->putTask($data, $tubename, 0);
            }
         } catch(Exception $e) {}
         return true;
      }
      
      return false;
   }
   
   /**
    * get jobseeker`s email by id
    * @param number $jobSeekerId
    * @return NULL|Ambigous <mixed, NULL>
    */
   public static function getJobSeekerEmail($jobSeekerId = 0) {
      if(empty($jobSeekerId))
         return null;
      $user = PFactory::getMasterUser();
      $db = PFactory::getDbo();
      $query = 'SELECT email FROM *PREFIX*hr_jobseeker WHERE id = ' . intval($jobSeekerId);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadResult();
   }

}
